package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.kcs.common.utils.Constant;
import com.kcs.fxmargin.bo.OverLimitSummaryReportBO;

public class OverLimitSummaryReportDAO {
	
	private Logger log = Logger.getLogger(Constant.LOG_FX);
	
	public OverLimitSummaryReportBO getOVerLimitSummaryList(OverLimitSummaryReportBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<OverLimitSummaryReportBO> resultList = null;
		OverLimitSummaryReportBO olsr = null;
		
		log.info("Begin : OverLimitSummaryReportDAO : getOVerLimitSummaryList :");
		
		try{
			sql = "with tmp as ( "+
				  " select cif_no, MAX(as_of_date) as_of_date "+
				  " from t_deals_sum  "+
				  " where active_status='A' "+
				  //" and MONTH(as_of_date) like '%' "+
				  " group by cif_no "+
				  " ) "+
				  " select s.cif_no, c.cust_name, 'PRC_FXM' pre_type, s.pre_settle_risk cash_bal, isnull(s.pre_settle_used, 0) tot_used "+
				  " , isnull((s.pre_settle_risk-s.pre_settle_used), 0) tot_avail, '1' type_order "+
				  " from t_deals_sum s left join m_cust_info c on s.cif_no=c.cif_no, tmp  "+
				  " where s.active_status='A' "+
				  " and s.cif_no=tmp.cif_no "+
				  " and s.as_of_date = tmp.as_of_date "+
				  " and isnull((s.pre_settle_risk-s.pre_settle_used), 0) < 0 "+
				  " union all "+
				  " select s.cif_no, c.cust_name, 'NOC_FXM' pre_type, s.notional_limit cash_bal, isnull(s.notional_used, 0) tot_used "+ 
				  " , isnull((s.notional_limit-s.notional_used), 0) tot_avail, '2' type_order "+
				  " from t_deals_sum s left join m_cust_info c on s.cif_no=c.cif_no, tmp  "+
				  " where s.active_status='A' "+
				  " and s.cif_no=tmp.cif_no "+
				  " and s.as_of_date = tmp.as_of_date "+
				  " and isnull((s.notional_limit-s.notional_used), 0) < 0 "+
				  " order by cust_name, cif_no, type_order";
			
			log.info(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<OverLimitSummaryReportBO>();
				
				while(rs.next()){
					olsr = new OverLimitSummaryReportBO();
					olsr.setCifNo(toStringNotNull(rs.getString("cif_no")));
					olsr.setCustName(toStringNotNull(rs.getString("cust_name")));
					olsr.setPreType(toStringNotNull(rs.getString("pre_type")));
					olsr.setCashBal(toStringNotNull(rs.getString("cash_bal")));
					olsr.setTotUsed(toStringNotNull(rs.getString("tot_used")));
					olsr.setTotAvail(toStringNotNull(rs.getString("tot_avail")));
					olsr.setTypeOrder(toStringNotNull(rs.getString("type_order")));

					resultList.add(olsr);
				}
				
				bo.setOverLimitSummaryList(resultList);
			}
			
			log.info("resultList size = "+ ((resultList==null)?"null":resultList.size()+""));
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		log.info("End : OverLimitSummaryReportDAO : getOVerLimitSummaryList :");
		return bo;
	}
	
	private String toStringNotNull(Object o)throws Exception{
		return (o==null)?"":o.toString();
	}
}
